﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.ETL.Infrastructure.SQLServer
{
    public static class SQLServer2008
    {
        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableId"></param>
        /// <returns></returns>
        public static Table GetTable(SqlConnection connection, int tableId)
        {
            string sql = "select id,name from sysobjects where xtype='u' and id=@id";
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = connection;
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Parameters.Add(new SqlParameter("@id", tableId));

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);

            List<Table> tables = new List<Table>();
            for (int index = 0; index < dataTable.Rows.Count; index++)
            {
                return new Table(Convert.ToInt32(dataTable.Rows[index]["id"]), Convert.ToString(dataTable.Rows[index]["name"]));
            }

            return null;
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static List<Table> GetTables(SqlConnection connection, string filter)
        {
            string sql = "select id,name from sysobjects where xtype='u'";
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = connection;
            sqlCommand.CommandText = sql;

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);

            List<Table> tables = new List<Table>();
            for (int index = 0; index < dataTable.Rows.Count; index++)
            {
                tables.Add(new Table(Convert.ToInt32(dataTable.Rows[index]["id"]), Convert.ToString(dataTable.Rows[index]["name"])));
            }

            return tables;
        }

        /// <summary>
        /// 获取数据列
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableId"></param>
        /// <returns></returns>
        public static List<Column> GetColumns(SqlConnection connection, int tableId)
        {
            string sql = @"	select
SC.name as [Name],
SC.colid as [Index],
ST.name as [Type],
SC.length as [Length],
SC.isnullable as [Nullable],
PK = case when (SIK.indid is null) then 0 else 1 end	
from syscolumns as SC
inner join systypes as ST on SC.xusertype = ST.xusertype
left join sysindexkeys as SIK on SIK.id = SC.id and SIK.colid = SC.colid
where SC.id=@tableid order by SC.colorder";

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = connection;
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Parameters.Add(new SqlParameter("@tableid", tableId));

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);

            List<Column> columns = new List<Column>();
            for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
            {
                string name = Convert.ToString(dataTable.Rows[rowIndex]["Name"]);
                string type = Convert.ToString(dataTable.Rows[rowIndex]["Type"]);
                int index = Convert.ToInt32(dataTable.Rows[rowIndex]["Index"]);
                int length = Convert.ToInt32(dataTable.Rows[rowIndex]["Length"]);
                bool nullable = Convert.ToBoolean(dataTable.Rows[rowIndex]["Nullable"]);
                bool pk = Convert.ToBoolean(dataTable.Rows[rowIndex]["PK"]);

                Column column = new Column();
                column.Name = name;
                column.Index = index;
                column.IsNullable = nullable;
                column.Length = ResolveLength(type, length);
                column.Type = type;
                column.PK = pk;
                columns.Add(column);
            }

            return columns;
        }

        private static int ResolveLength(string type, int length)
        {
            if (string.Equals(type, "nvarchar", StringComparison.CurrentCultureIgnoreCase))
            {
                return length / 2;
            }
            return length;
        }
    }
}
